<?php

namespace app\api\controller;

use app\common\controller\Api;

use app\common\model\Contracts as ContractsModel;
use app\common\model\Customer as CustomerModel;
use app\common\model\User;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Request;
use think\Db;
use app\common\service\UserPermissionService;

use app\common\model\PaymentPlan as PaymentPlanModel;
/**
 * 首页接口
 */
class Paymentplan extends Api
{
    protected $noNeedLogin = [];
    protected $noNeedRight = ['*'];
    public function getStatistics()
    {
        $paymentPlan = new \app\common\model\PaymentPlan();
        $userId = $this->auth->id;

        // 1. 全部
        $whereAll = [];
        $whereAll['is_delete'] = 0;


        //数据权限
        $userPermissionService = new UserPermissionService();
        $staffids=$userPermissionService->getUsersByPermission($this->auth->id);
        $whereAll['staff_id']=['in',$staffids];


        $totalAll = $paymentPlan->where($whereAll)->count();

        // 2. 我负责的
        $whereMine = $whereAll;
        $whereMine['staff_id'] = $userId;
        $totalMine = $paymentPlan->where($whereMine)->count();

        // 3. 下属负责的
        $staffids = Db::name('user')->where('leader_id', $userId)->column('id');
        $whereSub = $whereAll;
        if ($staffids) {
            $whereSub['staff_id'] = ['in', $staffids];
            $totalSub = $paymentPlan->where($whereSub)->count();
        } else {
            $totalSub = 0;
        }
        // 4. 今日应收款
        $today = date('Y-m-d');
        $whereToday = $whereAll;
        $whereToday['date'] = $today;
        $whereToday['status'] = '待回款';; // 未回款
        $totalToday = $paymentPlan->where($whereToday)->count();

        // 5. 本月应收款
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');
        $whereMonth = $whereAll;
        $whereMonth['date'] = ['between', [$monthStart, $monthEnd]];
        $whereMonth['status'] = '待回款';;
        $totalMonth = $paymentPlan->where($whereMonth)->count();

        // 6. 回款到期（到今天为止未回款）
        $whereDue = $whereAll;
        $whereDue['date'] = ['<=', $today];
        $whereDue['status'] = '待回款';
        $totalDue = $paymentPlan->where($whereDue)->count();

        $data = [
            ['type' => 1,'title' => '全部', 'value' => $totalAll],
            ['type' => 2,'title' => '我负责的', 'value' => $totalMine],
            ['type' => 3,'title' => '下属负责的', 'value' => $totalSub],
            ['type' => 4,'title' => '今日应收款', 'value' => $totalToday],
            ['type' => 5,'title' => '本月应收款', 'value' => $totalMonth],
            ['type' => 6,'title' => '回款到期', 'value' => $totalDue],
        ];

        $this->success('success', $data);
    }
    /**
     * 构建查询条件
     * @param array $params 查询参数
     * @param int $type 查询类型
     * @return array
     */
    private function buildWhere($params, $type)
    {
        $where = [];
        $where['a.is_delete'] = 0;
        

        // 数据权限
        $userPermissionService = new UserPermissionService();
        $staffids=$userPermissionService->getUsersByPermission($this->auth->id);
        $where['a.staff_id']=['in',$staffids];

        // 搜索关键词
        $searchKey = isset($params['searchKey']) ? $params['searchKey'] : '';
        if ($searchKey) {
            $where['c.customerName|c.phone|c.uidipin'] = ['like', "%{$searchKey}%"];
        }

        // 类型筛选
        switch ($type) {
            case 1: // 全部
                break;
            case 2: // 我负责的
                $where['a.staff_id'] = $this->auth->id;
                break;
            case 3: // 下属负责的
                $staffids = Db::name('user')->where('leader_id', $this->auth->id)->column('id');
                if ($staffids) {
                    $where['a.staff_id'] = ['in', $staffids];
                } else {
                    $where['a.staff_id'] = 0; // 不会有结果
                }
                break;
            case 4: // 今日应收款
                $today = date('Y-m-d');
                $where['a.date'] = $today;
                $where['a.status'] = '待回款';
                break;
            case 5: // 本月应收款
                $monthStart = date('Y-m-01');
                $monthEnd = date('Y-m-t');
                $where['a.date'] = ['between', [$monthStart, $monthEnd]];
                $where['a.status'] = '待回款';
                break;
            case 6: // 回款到期
                $today = date('Y-m-d');
                $where['a.date'] = ['<=', $today];
                $where['a.status'] = '待回款';
                break;
            default:
                break;
        }

        return $where;
    }

    /**
     * 获取查询数据
     * @param array $where 查询条件
     * @param int $page 页码
     * @param int $pageSize 每页数量
     * @param bool $isExport 是否为导出查询
     * @return array 返回查询结果
     */
    private function getQueryData($where, $page = 1, $pageSize = 20, $isExport = false)
    {
        $query = Db::name('payment_plan a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->join('contracts co', 'a.contract_id = co.id', 'LEFT')
            ->field('a.*, c.id as customerID,c.customerName, c.uidipin,u.id as staff_id,u.name as staffName,us.id as user_id,us.name as userName,co.id as contractId,co.contractName')
            ->where($where)
            ->order('a.id desc');

        if ($isExport) {
            $list = $query->select();
        } else {
            $list = $query->page($page, $pageSize)->select();
        }

        $total = Db::name('payment_plan a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->join('contracts co', 'a.contract_id = co.id', 'LEFT')
            ->field('a.*, c.id as customerID,c.customerName, c.uidipin,u.id as staff_id,u.name as staffName')
            ->where($where)
            ->count();

        // 格式化数据
        foreach ($list as $key => $value) {
            $customer = ['id' => $value['customerID'], 'customerName' => $value['customerName'], 'uidipin' => $value['uidipin']];
            $staff = ['id' => $value['staff_id'], 'name' => $value['staffName']];
            $user = ['id' => $value['user_id'], 'name' => $value['userName']];
            $contract = ['id' => $value['contractId'], 'contractName' => $value['contractName']];
            $list[$key]['customer'] = $customer;
            $list[$key]['contract'] = $contract;
            $list[$key]['staff'] = $staff;
            $list[$key]['user'] = $user;
        }

        return [
            'list' => $list,
            'total' => $total
        ];
    }

    public function getPaymentPlanList()
    {
        // 获取分页参数
        $page = $this->request->post('page', 1);
        $pageSize = $this->request->post('pageSize', 20);
        $type = $this->request->post('type', 1);
        $params = $this->request->post(); // 筛选条件
        $action = $this->request->post('action', 'list'); // 新增：action参数，list或export
        
        // 使用统一的buildWhere方法构建查询条件
        $where = $this->buildWhere($params, $type);
        
     
    
        // 如果是导出操作
        if ($action === 'export') {
            return $this->handleExport($where, $params);
        }
        // 如果是删除操作
        if ($action === 'delete') {
            return $this->handleDelete($where, $params);
        }
        
        // 获取数据
        $result = $this->getQueryData($where, $page, $pageSize, false);

        $this->success('请求成功', $result);
    }

    /**
     * 获取导出字段配置
     */
    public function getExportFields()
    {
        $fieldMap = [
            'status' => '回款状态',
            'customer_id' => '所属客户',
            'contract_id' => '所属合同',
            'repaid_amount' => '已回款金额',
            'unrepaid_amount' => '未回款金额',
            'amount' => '计划回款金额',
            'date' => '计划回款日期',
            'method' => '计划回款方式',
            'remind_days' => '提前几天提醒',
            'remind_date' => '提醒日期',
            'remark' => '备注信息',
            'user_id' => '创建人',
            'staff_id' => '负责人',
            'department_id' => '部门',
            'createtime' => '创建时间',
            'updatetime' => '更新时间',
        ];

        $this->success('获取成功', ['fields' => $fieldMap]);
    }

    public function getSelectPaymentPlan()
    {
        // 获取分页参数
        $page = $this->request->post('page', 1);
        $pageSize = $this->request->post('pageSize', 20);
        $customerId = $this->request->post('customerId');

        $where=[];
        $where['a.customer_id']=$customerId;
        $where['a.is_delete']=0;
        // 获取数据
        $result = $this->getQueryData($where, $page, $pageSize, false);

        $this->success('请求成功', $result);
    }


    /**
     * 处理导出功能
     * @param array $where 查询条件
     * @param array $params 请求参数
     * @return \think\response\Json
     */
    private function handleExport($where, $params)
    {
        // 获取导出参数
        $export_type = $this->request->post('export_type', 'all'); // 导出类型：selected, currentPage, all
        $fields = $this->request->post('fields'); // 选中的字段
        $selectedIds = $this->request->post('ids', []); // 选中的ID列表

        // 如果没有选择字段，返回错误
        if (empty($fields)) {
            $this->error('请选择要导出的字段');
        }
        $fields = explode(',', $fields);

        // 根据导出类型构建查询
        $paymentPlan = new \app\common\model\PaymentPlan();
        switch ($export_type) {
            case 'selected':
                if (empty($selectedIds)) {
                    $this->error('请选择要导出的数据');
                }
                $list = $paymentPlan->alias('a')
                    ->join('customer c', 'a.customer_id = c.id', 'LEFT')
                    ->join('user u', 'a.staff_id = u.id', 'LEFT')
                    ->join('contracts co', 'a.contract_id = co.id', 'LEFT')
                    ->field('a.*, c.customerName, c.uidipin, u.name as staffName, co.contractName')
                    ->where($where)
                    ->whereIn('a.id', $selectedIds)
                    ->select();
                break;
            case 'currentPage':
                $list = $paymentPlan->alias('a')
                    ->join('customer c', 'a.customer_id = c.id', 'LEFT')
                    ->join('user u', 'a.staff_id = u.id', 'LEFT')
                    ->join('contracts co', 'a.contract_id = co.id', 'LEFT')
                    ->field('a.*, c.customerName, c.uidipin, u.name as staffName, co.contractName')
                    ->where($where)
                    ->whereIn('a.id', $selectedIds)
                    ->select();
                break;
            case 'all':
            default:
                $list = $paymentPlan->alias('a')
                    ->join('customer c', 'a.customer_id = c.id', 'LEFT')
                    ->join('user u', 'a.staff_id = u.id', 'LEFT')
                    ->join('contracts co', 'a.contract_id = co.id', 'LEFT')
                    ->field('a.*, c.customerName, c.uidipin, u.name as staffName, co.contractName')
                    ->where($where)
                    ->order('a.id', 'desc')
                    ->select();
                break;
        }

        // 创建新的 Spreadsheet 对象
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 字段映射表
        $fieldMap = [
            'status' => '回款状态',
            'customer_id' => '所属客户',
            'contract_id' => '所属合同',
            'repaid_amount' => '已回款金额',
            'unrepaid_amount' => '未回款金额',
            'amount' => '计划回款金额',
            'date' => '计划回款日期',
            'method' => '计划回款方式',
            'remind_days' => '提前几天提醒',
            'remind_date' => '提醒日期',
            'remark' => '备注信息',
            'user_id' => '创建人',
            'staff_id' => '负责人',
            'department_id' => '部门',
            'createtime' => '创建时间',
            'updatetime' => '更新时间',
        ];

        // 写入表头
        $col = 1;
        foreach ($fields as $field) {
            if (isset($fieldMap[$field])) {
                $sheet->setCellValueByColumnAndRow($col, 1, $fieldMap[$field]);
                $col++;
            }
        }


        // 写入数据
        $row = 2;
        foreach ($list as $item) {
            $col = 1;
            foreach ($fields as $field) {
                $value = $item[$field] ?? '';

                // 处理特殊字段
                switch ($field) {
                    case 'user_id':
                        // 关联用户表获取创建人姓名
                        if ($value) {
                            $user = Db::name('user')->where('id', $value)->field('name')->find();
                            $value = $user ? $user['name'] : '';
                        }
                        break;
                    case 'staff_id':
                        // 关联用户表获取员工姓名
                        if ($value) {
                            $user = Db::name('user')->where('id', $value)->field('name')->find();
                            $value = $user ? $user['name'] : '';
                        }
                        break;
                    case 'contract_id':
                        // 从关联的合同数据中获取合同名称
                        $value = $item['contractName'] ?? '';
                        break;
                    case 'customer_id':
                        // 从关联的客户数据中获取客户名称
                        $value = $item['customerName'] ?? '';
                        break;
                    case 'repaid_amount':
                        // 从合同表获取已回款金额
                        $contract = Db::name('contracts')->where('id', $item['contract_id'])->find();
                        $value = $contract ? $contract['repaid_amount'] : 0;
                        break;
                    case 'unrepaid_amount':
                        // 计算未回款金额
                        $contract = Db::name('contracts')->where('id', $item['contract_id'])->find();
                        if ($contract) {
                            $value = $contract['amount'] - $contract['repaid_amount'];
                        } else {
                            $value = 0;
                        }
                        break;
                    case 'department_id':
                        // 关联部门表获取部门名称
                        if ($value) {
                            $dept = Db::name('department')->where('id', $value)->field('title')->find();
                            $value = $dept ? $dept['title'] : '';
                        }
                        break;
                    case 'status':
                        // 回款状态处理
                        $value = $value == 1 ? '已回款' : '未回款';
                        break;
                    case 'created_at':
                    case 'updated_at':
                    case 'createtime':
                    case 'updatetime':
                        $value = $value ? date('Y-m-d H:i:s', strtotime($value)) : '';
                        break;
                    case 'date':
                    case 'remind_date':
                        $value = $value ? date('Y-m-d', strtotime($value)) : '';
                        break;
                }
                $sheet->setCellValueByColumnAndRow($col, $row, $value);
                $col++;
            }
            $row++;



        }

        // 设置列宽
        for ($i = 1; $i <= count($fields); $i++) {
            $sheet->getColumnDimensionByColumn($i)->setWidth(30);
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ],
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'E0E0E0']
            ]
        ];
        $sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->applyFromArray($headerStyle);

        // 设置数据区域样式
        $dataStyle = [
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ]
        ];
        $sheet->getStyle('A2:' . $sheet->getHighestColumn() . ($row - 1))->applyFromArray($dataStyle);

        // 生成文件名
        $filename = '回款计划数据_' . date('YmdHis') . '.xlsx';

        // 确保目录存在
        $savePath = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'excel';
        if (!is_dir($savePath)) {
            mkdir($savePath, 0755, true);
        }

        // 完整的文件保存路径
        $filePath = $savePath . DS . $filename;

        // 保存文件
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($filePath);

        // 返回文件URL给前端
        $request = \think\Request::instance();
        $domain = $request->domain();
        $fileUrl = $domain . '/uploads/excel/' . $filename;
        $this->success('导出成功', ['url' => $fileUrl]);
    }

    /**
     * 导出数据
     */


    public function create()
    {
        $params = $this->request->post();

        // 验证必填字段
        if (empty($params['customer_id']) || empty($params['contract_id']) || empty($params['amount'])) {
            $this->error('缺少必填参数');
        }

        // 处理日期格式
        $remind_date = !empty($params['remind_date']) ? date('Y-m-d', strtotime($params['remind_date'])) : date('Y-m-d');
        $registrationDate = !empty($params['date']) ? date('Y-m-d', strtotime($params['date'])) : date('Y-m-d');
        $paymentPlan = new \app\common\model\PaymentPlan();

        // 保存回款记录
        $data = [
            'customer_id' => $params['customer_id'],
            'contract_id' => $params['contract_id'],
            'staff_id' => $this->auth->id,
            'amount' => $params['amount'],
            'method' => $params['method'] ?? '',
            'date' => $registrationDate,
            'createtime' => date('Y-m-d H:i:s', time()),
            'remark' => $params['remark'] ?? '',
            'remind_date' => $remind_date,
            'period' => $params['period'] ?? '',
            'remind_days' => $params['remind_days'] ?? '',
        ];
        $paymentPlan->save($data);

        $this->success('回款计划创建成功');

    }

    public function edit()
    {
        $params = $this->request->post();

        // 验证必填字段
        if (empty($params['amount'])) {
            $this->error('缺少必填参数');
        }
        // 处理日期格式
        $remind_date = !empty($params['remind_date']) ? date('Y-m-d', strtotime($params['remind_date'])) : date('Y-m-d');
        $registrationDate = !empty($params['date']) ? date('Y-m-d', strtotime($params['date'])) : date('Y-m-d');
        $paymentPlan = new \app\common\model\PaymentPlan();
        // 保存回款记录
        $data = [
            'amount' => $params['amount'],
            'method' => $params['method'] ?? '',
            'date' => $registrationDate,
            'createtime' => date('Y-m-d H:i:s', time()),
            'remark' => $params['remark'] ?? '',
            'remind_date' => $remind_date,
            'period' => $params['period'] ?? '',
            'remind_days' => $params['remind_days'] ?? '',
        ];
        $paymentPlan->where('id',$params['id'])->update($data);

        $this->success('回款记录更新成功');

    }

    public function batchcreate()
    {
        $list = $this->request->post('list/a'); // 获取数组参数，list为前端传参key


        if (empty($list) || !is_array($list)) {
            $this->error('参数错误');
        }

        $staff_id = $this->auth->id;
        $now = date('Y-m-d H:i:s');

        $data = [];
        foreach ($list as $k=> $item) {
            // 校验必填
            if (empty($item['customer_id']) || empty($item['contract_id']) || empty($item['date'])) {
                continue; // 跳过不完整的
            }
            $data[] = [
                'customer_id'   => $item['customer_id'],
                'contract_id'   => $item['contract_id'],
                'staff_id'      => $staff_id,
                'amount'        => $item['amount'],
                'method'        => $item['method'] ?? '',
                'date'          => $item['date'],
                'createtime'    => $now,
                'remark'        => $item['remark'] ?? '',
                'remind_date'   => $item['date'], // 你可以根据需要调整
                'period'        =>$k+1,
                'remind_days'   => $item['remindDays'] ?? '',
                'actual_amount' => 0,
            ];
        }

        if (empty($data)) {
            $this->error('没有有效数据');
        }

        $paymentPlanModel = new \app\common\model\PaymentPlan();
        $result = $paymentPlanModel->saveAll($data);

        if ($result) {
            $this->success('批量创建成功');
        } else {
            $this->error('批量创建失败');
        }
    }

    public function batchDelete(){
        $ids = $this->request->post('ids', '');
        $ids=explode(',',$ids);


        $find=Db::name('payment_record')->where('is_delete',0)->where('plan_id','in',$ids)->find();
        if($find){
            $this->error('有数据关联，不能删除');
        }

        $paymentPlanModel = new \app\common\model\PaymentPlan();
        $paymentPlanModel->whereIn('id',$ids)->update(['is_delete'=>1]);
        $this->success();
    }

    public function exportExcel()
    {
        // 获取导出参数
        $exportType = $this->request->post('type', 'all'); // 导出类型：selected, currentPage, all
        $fields = $this->request->post('fields'); // 选中的字段
        $selectedIds = $this->request->post('ids', []); // 选中的ID列表
        $params = $this->request->post(); // 筛选条件
        $filterType = $this->request->post('filterType', 1); // 筛选类型

        // 如果没有选择字段，返回错误
        if (empty($fields)) {
            $this->error('请选择要导出的字段');
        }
        $fields = explode(',', $fields);

        // 构建查询条件
        $where = $this->buildWhere($params, $filterType);

        // 根据导出类型处理查询条件
        switch ($exportType) {
            case 'selected':
                if (empty($selectedIds)) {
                    $this->error('请选择要导出的数据');
                }
                $where['a.id'] = ['in', $selectedIds];
                break;
            case 'currentPage':
                if (empty($selectedIds)) {
                    $this->error('请选择要导出的数据');
                }
                $where['a.id'] = ['in', $selectedIds];
                break;
            case 'all':
            default:
                // 使用完整的查询条件
                break;
        }

        // 获取数据
        $result = $this->getQueryData($where, 1, 0, true);
        $list = $result['list'];

        // 字段映射表
        $fieldMap = [
            'status' => '回款状态',
            'customer_id' => '所属客户',
            'contract_id' => '所属合同',
            'repaid_amount' => '已回款金额',
            'unrepaid_amount' => '未回款金额',
            'amount' => '计划回款金额',
            'date' => '计划回款日期',
            'method' => '计划回款方式',
            'remind_days' => '提前几天提醒',
            'remind_date' => '提醒日期',
            'remark' => '备注信息',
            'user_id' => '创建人',
            'staff_id' => '负责人',
            'department_id' => '部门',
            'createtime' => '创建时间',
            'updatetime' => '更新时间',
        ];


        // 写入表头
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $col = 1;
        foreach ($fields as $field) {
            if (isset($fieldMap[$field])) {
                $sheet->setCellValueByColumnAndRow($col, 1, $fieldMap[$field]);
                $col++;
            }
        }

        // 写入数据
        $row = 2;
        foreach ($list as $item) {
            $col = 1;
            foreach ($fields as $field) {
                $value = '';
                // 处理特殊字段
                switch ($field) {
                    case 'user_id':
                        // 需要单独查询创建人信息
                        $user = Db::name('user')->where('id', $item['user_id'])->value('name');
                        $value = $user ?: '';
                        break;
                    case 'staff_id':
                        $value = $item['staff']['name'] ?? '';
                        break;
                    case 'contract_id':
                        $value = $item['contract']['contractName'] ?? '';
                        break;
                    case 'customer_id':
                        $value = $item['customer']['customerName'] ?? '';
                        break;
                    case 'repaid_amount':
                        // 从合同表获取已回款金额
                        $contract = Db::name('contracts')->where('id', $item['contract_id'])->find();
                        $value = $contract ? $contract['repaid_amount'] : 0;
                        break;
                    case 'unrepaid_amount':
                        // 计算未回款金额
                        $contract = Db::name('contracts')->where('id', $item['contract_id'])->find();
                        if ($contract) {
                            $value = $contract['amount'] - $contract['repaid_amount'];
                        } else {
                            $value = 0;
                        }
                        break;
                    case 'department_id':
                        $staff = new \app\admin\model\User();
                        $department = $staff
                            ->alias('c')
                            ->join('fa_department d', 'c.department_id = d.id', 'left')
                            ->field('d.title')
                            ->where('c.id', $item['staff_id'])->find();
                        $value = $department ? $department['title'] : '';
                        break;
                    default:
                        $value = $item[$field] ?? '';
                        break;
                }
                $sheet->setCellValueByColumnAndRow($col, $row, $value);
                $col++;
            }
            $row++;
        }

        // 设置列宽
        for ($i = 1; $i <= count($fields); $i++) {
            $sheet->getColumnDimensionByColumn($i)->setWidth(15);
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
            ],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'E0E0E0']
            ]
        ];
        $sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->applyFromArray($headerStyle);

        // 设置数据区域样式
        $dataStyle = [
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
            ],
            'alignment' => ['vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER]
        ];
        $sheet->getStyle('A2:' . $sheet->getHighestColumn() . ($row - 1))->applyFromArray($dataStyle);

        // 生成文件名
        $filename = '回款计划数据_' . date('YmdHis') . '.xlsx';

        // 确保目录存在
        $savePath = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'excel';
        if (!is_dir($savePath)) {
            mkdir($savePath, 0755, true);
        }

        // 完整的文件保存路径
        $filePath = $savePath . DS . $filename;

        // 保存文件
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($filePath);

        // 返回文件URL给前端
        $request = \think\Request::instance();
        $domain = $request->domain();
        $fileUrl = $domain . '/uploads/excel/' . $filename;
        $this->success('导出成功', ['url' => $fileUrl]);
    }

    public function processImport_1()
    {
        $fileUrl = $this->request->post('fileUrl');
        $sheetName = $this->request->post('sheetName');

        if (empty($fileUrl) || empty($sheetName)) {
            $this->error('文件URL和Sheet名称不能为空');
        }

        // FastAdmin 的文件存储通常在 public/uploads/
        // 这里假设 $fileUrl 是从 FastAdmin 的上传接口返回的相对路径，例如 /uploads/20250617/xxx.xls
        $localFilePath = ROOT_PATH . 'public' . str_replace('/', DS, $fileUrl);


        if (!file_exists($localFilePath)) {
            $this->error('服务器上找不到文件，请确认文件已正确上传路径：' . $localFilePath);
        }

        $importedCount = 0;
        $updatedCount = 0;
        $skippedCount = 0;
        $failedCount = 0;
        $errorMessages = [];


        $spreadsheet = IOFactory::load($localFilePath);
        $sheet = $spreadsheet->getSheetByName($sheetName);

        if (!$sheet) {
            $this->error('指定的Sheet名称不存在：' . $sheetName);
        }

        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn(); // 比如 'BO'
        $maxColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        $header = [];
        for ($i = 1; $i <= $maxColIndex; $i++) {
            $header[] = trim($sheet->getCellByColumnAndRow($i, 1)->getValue());
        }

        // 定义 Excel 表头到数据库字段的映射 (根据 exportExcel 的 fieldMap )
        $excelToDbMap = [
            '期数'   => 'period',
            '所属合同'   => 'contractName',
            '所属客户'   => 'customerName',
            '计划回款金额' => 'amount',
            '计划回款日期' => 'date',
            '计划回款方式' => 'method',
            '提前几天提醒' => 'remind_days',
            '提醒日期'     => 'remind_date',

        ];
        // 缓存用户名称到ID的映射，避免循环查询
        $userCache = [];
        for ($rowNum = 2; $rowNum <= $highestRow; $rowNum++) {
            $rowData = [];
            for ($i = 1; $i <= $maxColIndex; $i++) {
                $headerIndex = $i - 1;
                if (!isset($header[$headerIndex])) continue;
                $headerName = $header[$headerIndex];
                $cellValue = $sheet->getCellByColumnAndRow($i, $rowNum)->getValue();
                if (isset($excelToDbMap[$headerName])) {
                    $dbField = $excelToDbMap[$headerName];
                    $rowData[$dbField] = trim($cellValue);
                }
            }


            if (isset($rowData['contractName'])) {
                $rowData['contract_id'] = Db::name('contracts')->where('contractName', $rowData['contractName'])->value('id');
                unset($rowData['contractName']);
            }

            if (isset($rowData['customerName'])) {
                $customer=Db::name('customer')->where('customerName', $rowData['customerName'])->find();
                if($customer){
                    $rowData['customer_id'] = $customer['id'];
                }else{
                    $failedCount++;
                    continue;
                }
                unset($rowData['customerName']);
            }

            if (isset($rowData['date'])) {
                if (is_numeric($rowData['date'])) {
                    $rowData['date'] = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($rowData['date'])->format('Y-m-d');
                }
            }
            if (isset($rowData['remind_date'])) {
                if (is_numeric($rowData['remind_date'])) {
                    $rowData['remind_date'] = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($rowData['remind_date'])->format('Y-m-d');
                }
            }
            $rowData['staff_id']=$this->auth->id;
            $importedCount++;
            // 默认值
            $paymentPlanModel = new  PaymentPlanModel();

            $paymentPlanModel->save($rowData);
        }
        Db::name('import_log')->insert(
            [
                'success'=>$importedCount,
                'fail'=>$failedCount,
                'url'=>$fileUrl,
                'name'=>$sheetName,
                'type'=>4,
                'total'=>$importedCount+$failedCount,
                'user_id'=>$this->auth->id,
                'createtime'=>date('Y-m-d H:i:s'),
            ]
        );
        $this->success('导入完成', [
            'imported' => $importedCount,
            'updated' => $updatedCount,
            'skipped' => $skippedCount,
            'failed' => $failedCount,
            'errors' => $errorMessages
        ]);

    }
    public function processImport()
    {
        $fileUrl = $this->request->post('fileUrl');
        $sheetName = $this->request->post('sheetName');
        $fieldMappings = $this->request->post('fieldMappings/a', []); // 前端传的映射关系
        $responsiblePersonType = $this->request->post('responsiblePersonType', 'excel'); // 负责人类型
        $responsibleUserId = $this->request->post('responsibleUserId', 0); // 指定负责人ID

        if (empty($fileUrl) || empty($sheetName) || empty($fieldMappings)) {
            $this->error('文件URL、Sheet名称和字段映射不能为空');
        }

        $localFilePath = ROOT_PATH . 'public' . str_replace('/', DS, $fileUrl);
        if (!file_exists($localFilePath)) {
            $this->error('服务器上找不到文件，请确认文件已正确上传路径：' . $localFilePath);
        }

        $importedCount = 0;
        $updatedCount = 0;
        $skippedCount = 0;
        $failedCount = 0;
        $errorMessages = [];

        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($localFilePath);
        $sheet = $spreadsheet->getSheetByName($sheetName);
        if (!$sheet) {
            $this->error('指定的Sheet名称不存在：' . $sheetName);
        }

        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $maxColIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        // 1. 生成 Excel列名 => 列号 的映射
        $excelHeaderMap = [];
        for ($i = 1; $i <= $maxColIndex; $i++) {
            $headerName = trim($sheet->getCellByColumnAndRow($i, 1)->getValue());
            if ($headerName !== '') {
                $excelHeaderMap[$headerName] = $i;
            }
        }

        // 2. 生成 系统字段 => [excel列名, 默认值, 列号] 的映射
        $fieldMap = [];
        foreach ($fieldMappings as $map) {
            $sysField = $map['systemField'];
            $excelField = $map['excelField'];
            $defaultValue = $map['defaultValue'];
            $colIndex = isset($excelHeaderMap[$excelField]) ? $excelHeaderMap[$excelField] : null;
            $fieldMap[$sysField] = [
                'excelField' => $excelField,
                'defaultValue' => $defaultValue,
                'colIndex' => $colIndex
            ];
        }

        $customerModel = new CustomerModel();
        $contractsModel = new ContractsModel();


        for ($rowNum = 2; $rowNum <= $highestRow; $rowNum++) { // 从第二行开始
            $rowData = [];
            $rowError = [];

            // 3. 按系统字段组装数据
            foreach ($fieldMap as $sysField => $info) {


                $value = '';
                if ($info['colIndex']) {
                    $cellValue = $sheet->getCellByColumnAndRow($info['colIndex'], $rowNum)->getValue();
                    $value = trim($cellValue);
                }
                if ($value === '' && $info['defaultValue'] !== '') {
                    $value = $info['defaultValue'];
                }

                // 特殊处理日期/时间字段
                if ($value === '' || $value === null) {
                    $value = null; // 空值设为 null
                } else {
                    if($sysField=='customer_id'){
                        $customer=$customerModel->where('customerName',$value)->where('is_delete',0)->find();
                        if($customer){
                            $value = $customer['id'];
                        }else{
                            $value='';
                        }
                    }
                    if($sysField=='contract_id'){
                        $contract=$contractsModel->where('contractName',$value)->where('is_delete',0)->find();
                        if($contract){
                            $value = $contract['id'];
                        }else{
                            $value='';
                        }
                    }
                    if($sysField=='staff_id'){
                        if($responsiblePersonType == 'excel'){
                            // 从Excel中获取负责人
                            $userModel=new User();
                            $user=$userModel->where('name',$value)->where('is_delete',0)->find();
                            if($user){
                                $value = $user['id'];
                            }else{
                                $value = null; // 设置为 null，在外层统一处理
                            }
                        }elseif($responsiblePersonType == 'assign'){
                            // 使用指定的负责人ID
                            $value = $responsibleUserId;
                        }else{
                            $value = null;
                        }
                    }
                }
                $rowData[$sysField] = $value;
            }

            unset($rowData['uidipin']);
            if (empty($rowData) || !array_filter($rowData)) {
                continue;
            }
            //创建人
            $rowData['user_id'] = $this->auth->id;

            // 验证 customer_id 和 staff_id 是否为空
            $validationErrors = [];
            if (empty($rowData['customer_id'])) {
                $validationErrors[] = "客户为空";
            }
            if (empty($rowData['staff_id'])) {
                $validationErrors[] = "负责人为空";
            }

            if (empty($rowData['contract_id'])) {
                $validationErrors[] = "合同为空";
            }

            if (!empty($validationErrors)) {
                $failedCount++;
                $errorMessages[] = "第 {$rowNum} 行：" . implode('、', $validationErrors) . "，跳过此条数据";
                continue;
            }

            $paymentPlanModel=new PaymentPlanModel();
            $res = $paymentPlanModel->save($rowData);
            if ($res !== false) {
                $importedCount++;
            } else {
                $failedCount++;
                $rowError[] = "插入失败（" . ($paymentPlanModel->getError() ?: '未知错误') . "）。";
                $errorMessages[] = "第 {$rowNum} 行：" . implode(' ', $rowError);
            }

        }

        Db::name('import_log')->insert([
            'success' => $importedCount,
            'fail' => $failedCount,
            'url' => $fileUrl,
            'name' => $sheetName,
            'type' => 4,
            'total' => $importedCount + $failedCount,
            'user_id' => $this->auth->id,
            'createtime' => date('Y-m-d H:i:s'),
        ]);

        $this->success('导入完成', [
            'imported' => $importedCount,
            'updated' => $updatedCount,
            'skipped' => $skippedCount,
            'failed' => $failedCount,
            'errors' => $errorMessages
        ]);
    }

    public function importlog()
    {
        // 获取分页参数
        $page = $this->request->post('page', 1);
        $pageSize = $this->request->post('pageSize', 20);

        // 查询条件
        $where = [];
        $userId = $this->auth->id;
        $where['type']=4;
        $where['user_id']=$userId;

        $importlog = new \app\common\model\Importlog();
        $total = $importlog->where($where)->count();
        $list = $importlog
            ->with(['user' => function($query) {
                $query->field('id,name');
            }])
            ->where($where)
            ->order('id', 'desc')
            ->page($page, $pageSize)
            ->select();

        $request = \think\Request::instance();
        $domain = $request->domain();
        foreach ($list as $k => $v) {
            $list[$k]['url'] = $domain . $v['url'];
        }
        $this->success('请求成功', ['list' => $list, 'total' => $total]);
    }

    /**
     * 处理删除功能（按条件删除，不传 ids 时按 where 条件删除）
     * @param array $where
     * @param array $params
     * @return \think\response\Json
     */
    private function handleDelete($where, $params)
    {
        // 使用与列表相同的关联查询获取 a.id 列表
        $ids = Db::name('payment_plan a')
            ->join('customer c', 'a.customer_id = c.id', 'LEFT')
            ->join('user u', 'a.staff_id = u.id', 'LEFT')
            ->join('user us', 'a.user_id = us.id', 'LEFT')
            ->join('contracts co', 'a.contract_id = co.id', 'LEFT')
            ->where($where)
            ->column('a.id');


        $find=Db::name('payment_record')->where('is_delete',0)->where('plan_id','in',$ids)->find();
        if($find){
            $this->error('有数据关联，不能删除');
        }

        if (empty($ids)) {
            $this->error('没有找到需要删除的记录');
        }

        // 按基础表ID软删除，避免别名歧义
        $result = Db::name('payment_plan')
            ->where('id', 'in', $ids)
            ->update([
                'is_delete' => 1,
                'deletetime' => time(),
            ]);

        $deletedCount = $result ?: 0;
        $this->success("删除成功，共删除 {$deletedCount} 条记录");
    }


}